package itcast.dao.impl;

import itcast.dao.StudentDao;
import itcast.domain.Admin;
import itcast.domain.Build;
import itcast.domain.Drom;
import itcast.domain.Studnet;
import itcast.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class StudentDaoImpl implements StudentDao {
    //实现template对象
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    //查找数据


    @Override
    public Studnet finddrom_exchangeByID(String student_username,String student_name) {
        //查找调换信息
        //使用jdbc操作数据库
        //1.定义sql
        String sql="SELECT Student_Username,Student_Name,Student_Sex,Drom_Name,Build_Name FROM student,drom,build " +
                "WHERE student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID and 1=1 and student_username=? or student_name=?";
        //query返回list集合，BeanPropertyRowMapper指定字节码
        return template.queryForObject(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class),student_username,student_name);

    }
    //根据学号查找数据

    @Override
    public List<Studnet> finddrom_exchangeByusername(Map<String, String[]> condition) {
        //1.定义模板初始化sql
//        String sql="select * from student where 1=1";
        String sql="SELECT Student_Username,Student_Name,Student_Sex,Drom_Name,Build_Name FROM student,drom,build\n" +
                "WHERE student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID and 1=1";
        StringBuilder sb=new StringBuilder(sql);
        //2.遍历map中value是否有值
        Set<String> keySet =condition.keySet();
        //定义参数集合
        List<Object> params=new ArrayList<Object>();
        for(String key :keySet){
            //获取value
            String value=condition.get(key)[0];
            //判断value是否有值
            if(value!=null && !"".equals(value)){
                sb.append(" and "+key+" = ? ");
                params.add(value);//条件的值
            }
        }
        System.out.println(sb.toString());
        System.out.println(params);
        List<Studnet> studnets=template.query(sb.toString(),new BeanPropertyRowMapper<Studnet>(Studnet.class),params.toArray());
        return studnets;
    }

    //调换宿舍
    @Override
    public void exchange_drom(Studnet studnet) {
        //1.定义sql语句
        String sql="update student,drom,build SET build.Build_Name=?, drom.drom_name=? where student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID AND 1=1 and Student_Name=?";
        //执行sql语句
        template.update(sql,studnet.getBuild_name(),studnet.getDrom_name(),studnet.getStudent_name());
        System.out.println(sql);
    }

    //学生迁入信息

    @Override
    public List<Studnet> findstudent_into() {
        //使用jdbc操作数据库
        //1.定义sql
        String sql="SELECT Student_Username,Student_Name,Student_Sex,student_Class,student_state FROM student WHERE  Student_State='未迁入'";
        //query返回list集合，BeanPropertyRowMapper指定字节码
        List<Studnet>  studnets=template.query(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class));

        return studnets;
    }
    //根据学号查询学生迁入信息

    @Override
    public Studnet findstudent_into_ID(String student_username) {
        //1.定义sql
        String sql="SELECT Student_Username,Student_Name,Student_Sex,student_Class,student_state FROM student WHERE  Student_State='未迁入' and Student_username=? ";
        //query返回list集合，BeanPropertyRowMapper指定字节码
        return template.queryForObject(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class),student_username);

    }
    //根据显示已有的楼宇与宿舍的信息

    @Override
    public List<Studnet> find_into_build_drom() {
        //String sql="SELECT Build_Name,Drom_Name,drom_type,drom_number FROM student,drom,build WHERE student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID and drom_number<6 ";
        String sql="SELECT distinct Build_Name,Drom_Name,drom_type,drom_number FROM student,drom,build WHERE drom.Drom_BuildID=build.Build_ID and drom_number<6 ";
        List<Studnet>  studnets=template.query(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class));

        return studnets;
    }
    //根据学号更新迁入信息

    @Override
    public void update_student_into(Studnet studnet) {
    String sql="select drom_id from drom,build where drom_buildid=build_id and build_name=? and drom_name=? ";

        //执行sql语句
      int  drom_id=template.queryForObject(sql,Integer.class,studnet.getBuild_name(),studnet.getDrom_name());
      String sql1="update student,drom set student_domitoryid=?, student_state='已迁入' where student_username=?";
      template.update(sql1,drom_id,studnet.getStudent_username());
     /* String sql2="insert into student(student_dromid) values(?)";
      template.update(sql)*/
      String sql3="update drom,build set drom_number=drom_number+1 where drom_buildid=build_id and drom_name=? and build_name=?";
      template.update(sql3,studnet.getDrom_name(),studnet.getBuild_name());


    }
    //查找学生管理信息

    @Override
    public List<Studnet> find_studentmanage() {
        String sql="Select * from student ";
        List<Studnet>  studnets=template.query(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class));

        return studnets;
    }
    //根据条件查找学生管理信息

    @Override
    public List<Studnet> findStudent_manageByData(Map<String, String[]> condition) {
        //1.定义模板初始化sql
//        String sql="select * from student where 1=1";
        String sql="select * from student where 1=1";
        StringBuilder sb=new StringBuilder(sql);
        //2.遍历map中value是否有值
        Set<String> keySet =condition.keySet();
        //定义参数集合
        List<Object> params=new ArrayList<Object>();
        for(String key :keySet){
            //获取value
            String value=condition.get(key)[0];
            //判断value是否有值
            if(value!=null && !"".equals(value)){
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//条件的值
            }
        }
        System.out.println(sb.toString());
        System.out.println(params);
        List<Studnet> studnets=template.query(sb.toString(),new BeanPropertyRowMapper<Studnet>(Studnet.class),params.toArray());
        return studnets;
    }

    //修改学生管理信息

    @Override
    public void updateStudent_manage(Studnet studnet) {
        //1.定义sql语句
        String sql="update student set Student_password=?, Student_Name=?,Student_sex=?,student_class=? where student_ID=?";
        //执行sql语句
        template.update(sql,studnet.getStudent_password(),
                studnet.getStudent_name(),studnet.getStudent_sex(),
                studnet.getStudent_class(),studnet.getStudent_id());
    }

    //根据id查找学生管理信息

    @Override
    public Studnet findStudnet_manageByid(int student_id) {
        //1.定义sql语句
        String sql="select * from student where 1=1 and student_ID=?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class),student_id);
    }

    //根据id删除学生管理信息

    @Override
    public void deletestudnet_managebyid(int student_id) {
        //1.定义sql
        String sql="delete from student where student_ID=?";
        //2.执行sql
        template.update(sql,student_id);
    }
    //添加学生数据

    @Override
    public void addStudent_manage(Studnet studnet) {
        //1.定义sql语句
        String sql="insert into student (student_username,student_password,student_name,student_sex,student_class,student_state) values(?,?,?,?,?,'未迁入')";
        //2.执行sql
        template.update(sql,studnet.getStudent_username(),studnet.getStudent_password(),
                studnet.getStudent_name(),studnet.getStudent_sex(),studnet.getStudent_class());
    }

    //学生登录
    @Override
    public Studnet studentdenglu(String student_username, String student_password) {
        //使用jdbc操作数据库
        //1.定义Sql
        try {
            String sql="select * from student where Student_Username=? and Student_Password=?";
            Studnet studnet = template.queryForObject(sql,new BeanPropertyRowMapper<Studnet>(Studnet.class),student_username,student_password);
            return studnet;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }
}

